/**
 * Title: DBUtil.java
 * Description:
 * author: kotomifi
 * date: Jul 19, 2014
 * ©2014 apping 社区
 */
package com.apping.demo.bbs.utils;

import java.util.ArrayList;
import java.util.List;

import com.apping.demo.bbs.bean.Comment;
import com.apping.demo.bbs.bean.NewsInfo;
import com.apping.demo.bbs.bean.NewsItem;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

/**
 *
 */
public class DBUtil {
	private SQLiteDatabase db;
	
	public DBUtil(SQLiteDatabase db) {
		this.db = db;
	}
	
	/**
	 * 向news_list 表中插入记录
	 */
	public void insert(NewsItem newsItem) {
		ContentValues newsValue = new ContentValues();
		newsValue.put("news_id", newsItem.getId());
		newsValue.put("news_title", newsItem.getTitle());
		newsValue.put("news_time", newsItem.getTime());
		newsValue.put("news_star", newsItem.getStar());
		newsValue.put("news_author", newsItem.getAuthor());
		try {
			db.insert("news_list", null, newsValue);
		} catch (Exception ex) {
			createTable();
			db.insert("news_list", null, newsValue);
		}
	}
	
	/**
	 * 向news_info 表中插入一条记录
	 * 若首次插入，则先创建数据库表
	 */
	public void insert(NewsInfo newsInfo) {
		// 插入news_info记录
		ContentValues newsValue = new ContentValues();
		newsValue.put("news_id", newsInfo.getId());
		newsValue.put("news_author", newsInfo.getAuthor());
		newsValue.put("news_time", newsInfo.getTime());
		newsValue.put("news_title", newsInfo.getTitle());
		newsValue.put("news_content", newsInfo.getContent());
		newsValue.put("news_star", newsInfo.getStar());
		try {
			db.insert("news_info", null, newsValue);
		} catch (Exception ex) {
			createTable();
			db.insert("news_info", null, newsValue);
		}
	}
	
	/**
	 * 插入评论列表
	 */
	public void insert(List<Comment> comments) {
		for (int i = 0; i < comments.size(); i++) {
			ContentValues commentValue = new ContentValues();
			commentValue.put("news_id", comments.get(i).getNewsId());
			commentValue.put("comment_author", comments.get(i).getAuthor());
			commentValue.put("comment_time", comments.get(i).getTime());
			commentValue.put("comment_content", comments.get(i).getContent());
			try {
				db.insert("news_comment", null, commentValue);
			} catch (SQLiteException ex) {
				createTable();
				db.insert("news_comment", null, commentValue);
			}
		}
	}
	
	/**
	 * 插入单条评论
	 */
	public void insert(Comment comment) {
		ContentValues commentValue = new ContentValues();
		commentValue.put("news_id", comment.getNewsId());
		commentValue.put("comment_author", comment.getAuthor());
		commentValue.put("comment_time", comment.getTime());
		commentValue.put("comment_content", comment.getContent());
		try {
			db.insert("news_comment", null, commentValue);
		} catch (SQLiteException ex) {
			createTable();
			db.insert("news_comment", null, commentValue);
		}
	}
	
	public void delete() {
		
	}
	
	public void update() {
		
	}
	
	/**
	 * 清空news_comment表
	 * 将news_comment表的自增加字段归零
	 */
	public void clearNewsComment() {
		db.execSQL("delete from news_comment");
		db.execSQL("delete from sqlite_sequence where name = 'news_comment'");
	}
	
	/**
	 * 清空news_info表
	 */
	public void clearNewsInfo() {
		db.execSQL("delete from news_info");
	}
	
	/**
	 * 清空news_list表
	 */
	public void clearNewsList() {
		db.execSQL("delete from news_list");
	}
	
	/**
	 * 创建评论表 (news_comment)
	 * 创建新闻表 (news_info)
	 * 创建新闻列表表 (news_list)
	 */
	public void createTable() {
		String sql_comment = "create table news_comment" +
				"(_id integer primary key autoincrement,"
				+ "news_id integer,"
				+ "comment_author text,"
				+ "comment_time long,"
				+ "comment_content text)";
		db.execSQL(sql_comment);
		
		String sql_news = "create table news_info" +
				"(news_id integer primary key,"
				+ "news_author text," 
				+ "news_time text,"
				+ "news_title text,"
				+ "news_content text,"
				+ "news_star integer)";
		db.execSQL(sql_news);
		
		String sql_list = "create table news_list" +
				"(news_id integer primary key,"
				+ "news_author text," 
				+ "news_time text,"
				+ "news_title text,"
				+ "news_star integer)";
		db.execSQL(sql_list);
	}
	
	/**
	 * 获取所有的新闻记录
	 */
	public void getAllNews(List<NewsInfo> newsList) {
		String sql = "select * from news_info";
		Cursor cursor = db.rawQuery(sql, null);
		NewsInfo mNews = new NewsInfo();
		while (cursor.moveToNext()) {
			mNews.setId(cursor.getInt(0));
			mNews.setAuthor(cursor.getString(1));
			mNews.setTime(cursor.getLong(2));
			mNews.setTitle(cursor.getString(3));
			mNews.setContent(cursor.getString(4));
			mNews.setStar(cursor.getInt(5));
			newsList.add(mNews);
		}
		cursor.close();
	}
	
	/**
	 * 根据news_id获取对应的评论列表
	 */
	public void getAllComments(int news_id, List<Comment> comments) {
		comments.clear();
		String sql = "select comment_author, comment_time,"
				+ "comment_content from news_comment where news_id =" + news_id;
		Cursor cursor = db.rawQuery(sql, null);
		Comment comment = new Comment();
		while (cursor.moveToNext()) {
			comment.setAuthor(cursor.getString(0));
			comment.setTime(cursor.getLong(1));
			comment.setContent(cursor.getString(2));
			comments.add(comment);
		}
		cursor.close();
	}
	
	/**
	 * 查询新闻列表
	 */
	public void getNewsList(List<NewsItem> newsList) {
		newsList.clear();
		String sql = "select * from news_list";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			NewsItem mNews = new NewsItem();
			mNews.setId(cursor.getInt(0));
			mNews.setAuthor(cursor.getString(1));
			mNews.setTime(cursor.getLong(2));
			mNews.setTitle(cursor.getString(3));
			mNews.setStar(cursor.getInt(4));
			newsList.add(mNews);
		}
		cursor.close();
	}
}